package com.netstar;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class SQLiteHelper extends SQLiteOpenHelper {

	/**
	 * Version logs, version 1 for test use.
	 * 
	 * <li>
	 * 1.23---------------2
	 * 
	 * <li>
	 * 1.42---------------3
	 * 
	 * <li>
	 * 1.43---------------4
	 */
	private static final int DATABASE_VERSION = 4;

	private static final String DATABASE_NAME = "network_statistics.db";

	private static SQLiteHelper sInstance;

	public static SQLiteHelper getInstance(Context context) {
		if (null == sInstance) {
			synchronized (SQLiteHelper.class) {
				if (null == sInstance) {
					sInstance = new SQLiteHelper(context.getApplicationContext());
				}
			}
		}
		return sInstance;
	}

	private SQLiteHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE IF NOT EXISTS apps(_id INTEGER PRIMARY KEY, " + "_pkg TEXT NOT NULL, "
				+ "_uid INTEGER DEFAULT 0, " + "_name TEXT NOT NULL)");

		db.execSQL("CREATE TABLE IF NOT EXISTS apps_day_stat(_id INTEGER PRIMARY KEY, " + "_app_id INTEGER, "
				+ "_received INTEGER DEFAULT 0, " + "_sent INTEGER DEFAULT 0, "
				+ "_received_since_boot INTEGER DEFAULT 0, " + "_sent_since_boot INTEGER DEFAULT 0, "
				+ "_updated INTEGER DEFAULT 0, " + "_day TEXT NOT NULL, " + "_sync INTEGER DEFAULT 0, "
				+ "_g_received INTEGER DEFAULT 0, " + "_g_sent INTEGER DEFAULT 0)");

		db.execSQL("CREATE TABLE IF NOT EXISTS excluded_apps(" + "_id INTEGER PRIMARY KEY, "
				+ "_mnc_id INTEGER DEFAULT 0, _pkg TEXT NOT NULL)");

		// TODO for test use
		ContentValues values = new ContentValues();
		values.put("_mnc_id", 1);
		values.put("_pkg", "com.aspire.mm");
		db.insert("excluded_apps", null, values);

		values = new ContentValues();
		values.put("_mnc_id", 1);
		values.put("_pkg", "com.feinno.felio");
		db.insert("excluded_apps", null, values);

		values = new ContentValues();
		values.put("_mnc_id", 1);
		values.put("_pkg", "com.cmcc.mobilevideo");
		db.insert("excluded_apps", null, values);

		values = new ContentValues();
		values.put("_mnc_id", 2);
		values.put("_pkg", "com.tencent.WBlog");
		db.insert("excluded_apps", null, values);

		db.execSQL("CREATE TABLE IF NOT EXISTS blocked_apps(_id INTEGER PRIMARY KEY, _uid INTEGER DEFAULT 0)");
		// TODO for test use

		db.execSQL("CREATE TABLE IF NOT EXISTS flow_set_apps(_id INTEGER PRIMARY KEY, _uid INTEGER DEFAULT 0, _flow INTEGER DEFAULT 0)");

		db.execSQL("CREATE VIEW view_day AS " + "SELECT apps_day_stat._id AS _id, "
				+ "apps_day_stat._received AS _received, " + "apps_day_stat._sent AS _sent, "
				+ "apps_day_stat._day AS _day, " + "apps._pkg AS _pkg, " + "apps._name AS _name, "
				+ "apps_day_stat._sync AS _sync, " + "apps._uid AS _uid, " + "excluded_apps._mnc_id AS _mnc_id, "
				+ "blocked_apps._id AS _blocked_id, " + "flow_set_apps._flow AS _flow, "
				+ "apps_day_stat._g_received AS _g_received, " + "apps_day_stat._g_sent AS _g_sent "
				+ "FROM apps_day_stat LEFT OUTER JOIN apps ON apps._id = apps_day_stat._app_id "
				+ "LEFT OUTER JOIN excluded_apps ON apps._pkg = excluded_apps._pkg "
				+ "LEFT OUTER JOIN blocked_apps ON apps._uid = blocked_apps._uid "
				+ "LEFT OUTER JOIN flow_set_apps ON apps._uid = flow_set_apps._uid");

		db.execSQL("CREATE TABLE IF NOT EXISTS net_timer(_id INTEGER PRIMARY KEY, " + "_3g_gprs INTEGER DEFAULT 0, "
				+ "_cmcc INTEGER DEFAULT 0, " + "_unicom INTEGER DEFAULT 0, " + "_china_net INTEGER DEFAULT 0, "
				+ "_wifi INTEGER DEFAULT 0, " + "_day TEXT NOT NULL, " + "_sync INTEGER DEFAULT 0)");

		db.execSQL("CREATE TABLE IF NOT EXISTS channel_day_stat(" + "_id INTEGER PRIMARY KEY, "
				+ "_3g_gprs INTEGER DEFAULT 0, " + "_3g_gprs_s_boot INTEGER DEFAULT 0, " + "_cmcc INTEGER DEFAULT 0, "
				+ "_unicom INTEGER DEFAULT 0, " + "_china_net INTEGER DEFAULT 0, " + "_wifi INTEGER DEFAULT 0, "
				+ "_wifi_last INTEGER DEFAULT 0, " + "_updated INTEGER DEFAULT 0, "
				+ "_day TEXT NOT NULL, _sync INTEGER DEFAULT 0)");

		db.execSQL("CREATE VIEW view_channel AS " + "SELECT channel_day_stat._3g_gprs AS _3g_gprs_d, "
				+ "channel_day_stat._cmcc AS _cmcc_d, " + "channel_day_stat._unicom AS _unicom_d, "
				+ "channel_day_stat._china_net AS _china_net_d, " + "channel_day_stat._wifi AS _wifi_d, "
				+ "channel_day_stat._day AS _day, " + "net_timer._3g_gprs AS _3g_gprs_t, "
				+ "net_timer._cmcc AS _cmcc_t, " + "net_timer._unicom AS _unicom_t, "
				+ "net_timer._china_net AS _china_net_t, " + "net_timer._wifi AS _wifi_t, " + "net_timer._id AS _id, "
				+ "net_timer._sync AS _sync "
				+ "FROM channel_day_stat LEFT OUTER JOIN net_timer ON channel_day_stat._day = net_timer._day");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP VIEW IF EXISTS view_day");
		db.execSQL("DROP VIEW IF EXISTS view_channel");
		db.execSQL("DROP TABLE IF EXISTS apps");
		db.execSQL("DROP TABLE IF EXISTS apps_day_stat");
		db.execSQL("DROP TABLE IF EXISTS net_timer");
		db.execSQL("DROP TABLE IF EXISTS channel_day_stat");
		db.execSQL("DROP TABLE IF EXISTS excluded_apps");
		db.execSQL("DROP TABLE IF EXISTS blocked_apps");
		db.execSQL("DROP TABLE IF EXISTS flow_set_apps");
		onCreate(db);
	}

}